package com.apes.scm.account.account.repository;

import com.apes.framework.jpa.repository.CustomRepository;
import com.apes.scm.account.account.model.Stock;
import com.apes.scm.masterdata.product.model.Product;
import com.apes.scm.masterdata.stock.model.Location;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;

import java.util.Date;
import java.util.List;
import java.util.Map;

/**
 * 商品库存帐
 * Created by HuJianlin on 2018-01-27.
 */
public interface StockRepository extends CustomRepository<Stock, Long> {

    Stock findByProductAndLocation(Product product, Location location);


    @Query("select distinct a.location,a.company from Stock a where a.location = :location")
    List<Stock> findAllByLocation(Location location);

    @Query("select distinct a.location,a.company from Stock a ")
    List<Stock> findAllStock();

    Stock findFirstByProductIdAndLocationIdAndUsableQtyGreaterThan(String productId, String locationId, double qty);

    @Query("SELECT T FROM Stock T WHERE T.location.id = :location_id and (T.product.id = :product_id or T.product.name like %:product_name%) and T.usableQty > 0 ")
    List<Stock> findAvailInventory(@Param("location_id") String location_id, @Param("product_id") String product_id, @Param("product_name") String product_name);

    @Query("SELECT T FROM Stock T WHERE (T.product.id = :product_id or T.product.name like %:product_name%) and T.usableQty - T.frozenQty > 0 " +
            "and T.location.valid <> false ")
    Page<Stock> findAvailInventoryQty(@Param("product_id") String product_id, @Param("product_name") String product_name, Pageable pageable);

    @Query("SELECT T FROM Stock T WHERE T.location.id = :location_id and T.usableQty > 0 ")
    List<Stock> findAllAvailInventory(@Param("location_id") String location_id);

    @Query("select new map(s.id as id, s.product.id as productId, s.product.name as productName," +
            " s.location.depot.id as depotId, s.location.depot.id as depotName, " +
            " s.location.id as locationId, s.location.name as locationName, " +
            " s.company.id as companyId, s.company.party.name as companyName, " +
            " s.usableQty as usableQty, s.updateDate as updateDate, " +
            " to_char(s.lastUpdatedStamp, 'yyyy-mm-dd hh24:mi:ss.FF3') as lastUpdatedStamp ) " +
            " FROM Stock s " +
            " where s.lastUpdatedStamp > :times or (s.id > :id and s.lastUpdatedStamp = :times)  " +
            " order by s.lastUpdatedStamp, s.id asc ")
    List<Map> findStocksByTs(@Param("times") Date times, @Param("id") long id, Pageable pageable);

    List<Stock> findByLocationDepotDeptIdAndLocationUsageIdAndProductIn(String deptId,String usageId,List list);

    @Query("select distinct s from Stock s left join s.product p where s.location.id = :locationId and s.usableQty > 0 " +
            " and p.freeSuppliesValid = true and p.id = :productId ")
    List<Stock> findStockByLocationAndProduct(@Param("locationId") String locationId, @Param("productId") String productId);

    @Query("select distinct s from Stock s where s.location.id = :locationId and s.custodyQty > 0 and s.usableQty > 0")
    List<Stock> findStockByLocation(@Param("locationId") String locationId);

    @Query(value = "SELECT * FROM V_QRYUABLE_QTY A WHERE A.LOCATION_ID = :location AND FREE_SUPPLIES_VALID IN :hcbj AND PRODUCT_GROUP_ID IN :wzly AND A.MARKETABLEQTY > 0 ", nativeQuery = true)
    Page<Map> findUsableQty(@Param("location")String locationId, @Param("hcbj") List<Integer> hcbj, @Param("wzly") List<String> wzly, Pageable pageable);

    @Query(value = "SELECT * FROM V_QRYUABLE_QTY A WHERE A.LOCATION_ID = :location AND FREE_SUPPLIES_VALID IN :hcbj AND PRODUCT_GROUP_ID IN :wzly AND A.MARKETABLEQTY > 0 AND EXISTS(SELECT 1 FROM MAS_PRODUCT T WHERE T.ID = A.PRODUCT_ID AND T.FA_CATEGORY_ID IS NULL)", nativeQuery = true)
    Page<Map> findUsableQty3(@Param("location")String locationId, @Param("hcbj") List<Integer> hcbj, @Param("wzly") List<String> wzly, Pageable pageable);

    @Query(value = "SELECT * FROM V_QRYUABLE_QTY A WHERE A.LOCATION_ID = :locationId AND (A.PRODUCT_ID = :productId OR A.PRODUCT_NAME LIKE %:productName%) AND A.MARKETABLEQTY > 0 " +
            " AND PRODUCT_GROUP_ID IN :wzly AND FREE_SUPPLIES_VALID IN :hcbj ", nativeQuery = true)
    Page<Map> findUsableQty2(@Param("locationId")String locationId, @Param("productId")String productId, @Param("productName")String productName, @Param("hcbj") List<Integer> hcbj, @Param("wzly") List<String> wzly, Pageable pageable);

    @Query(value = "SELECT * FROM V_QRYUABLE_QTY A WHERE A.LOCATION_ID = :locationId AND (A.PRODUCT_ID = :productId OR A.PRODUCT_NAME LIKE %:productName%) AND A.MARKETABLEQTY > 0 " +
            " AND PRODUCT_GROUP_ID IN :wzly AND FREE_SUPPLIES_VALID IN :hcbj AND EXISTS(SELECT 1 FROM mas_product T WHERE T.ID = A.PRODUCT_ID AND T.FA_CATEGORY_ID IS NULL)", nativeQuery = true)
    Page<Map> findUsableQty4(@Param("locationId")String locationId, @Param("productId")String productId, @Param("productName")String productName, @Param("hcbj") List<Integer> hcbj, @Param("wzly") List<String> wzly, Pageable pageable);
    /**
     * fetch data by company id and product id
     *
     * @param locationId 仓库编码
     * @param productId 商品编码
     * @return  Stock
     */
    @Query("SELECT T FROM Stock T WHERE T.location.id = :locationId and T.product.id = :productId  and T.usableQty > 0 ")
    List<Stock> findByLocationAndProduct(@Param("locationId") String locationId, @Param("productId") String productId);

    @Query("SELECT T FROM Stock T WHERE T.location.id = :locationId and T.product.id = :productId  and T.usableQty > 0 ")
    Stock findByLocationIdAndProductId(@Param("locationId") String locationId, @Param("productId") String productId);

    @Query(value = "" +
            "SELECT LEFTTEXT, COUNT(*) RIGHTTEXT FROM V_STOCK_QTY_WARNING WHERE (STOCK_QTY != 0 OR OLD_QTY != 0)" +
            "GROUP BY LEFTTEXT ", nativeQuery = true)
    List<Map> stockWarning();

    @Query(value = "" +
            "SELECT A.LOCATION_ID, " +
            "       (SELECT NAME FROM MAS_LOCATION WHERE ID = A.LOCATION_ID) LOCATION_NAME, " +
            "       A.PRODUCT_ID, " +
            "       (SELECT NAME FROM MAS_PRODUCT WHERE ID = A.PRODUCT_ID) PRODUCT_NAME, " +
            "       A.OLD_QTY, A.STOCK_QTY, " +
            "       (SELECT COUNT(*) FROM V_STOCK_QTY_WARNING WHERE LEFTTEXT = :lefttext  AND (STOCK_QTY != 0 OR OLD_QTY != 0)) TOTAL " +
            "  FROM V_STOCK_QTY_WARNING A " +
            "  WHERE LEFTTEXT = :lefttext AND (STOCK_QTY != 0 AND OLD_QTY != 0) ", nativeQuery = true)
    List<Map> stockWarningItem(@Param("lefttext") String lefttext);

    @Query(value = "" +
            "SELECT A.SQL " +
            "  FROM V_STOCK_QTY_WARNING A " +
            "  WHERE LEFTTEXT = :lefttext AND ROWNUM = 1", nativeQuery = true)
    List<Map> getStockWarningItemSql(@Param("lefttext") String lefttext);

    @Query(value = "SELECT PARENTS_ID FROM MAS_CLASSIFY WHERE ID IN (SELECT PARENTS_ID FROM MAS_CLASSIFY WHERE ID IN (SELECT CLASSIFY_ID FROM MAS_PRODUCT WHERE ID IN (SELECT PRODUCT_ID FROM ACC_STOCK WHERE " +
            "LOCATION_ID = (SELECT ID FROM MAS_LOCATION WHERE DEPOT_ID IN (SELECT ID FROM MAS_DEPOT WHERE DEPT_ID = :deptId) AND USAGE_ID = '1') " +
            "AND CUSTODY_QTY >0) GROUP BY CLASSIFY_ID) GROUP BY PARENTS_ID) GROUP BY PARENTS_ID",nativeQuery = true)
    List<String> findStockClassify(@Param("deptId") String deptId);

    @Query(value = "select * from ACC_STOCK where LOCATION_ID = :locationId and CUSTODY_QTY >0 and PRODUCT_ID in " +
            "(select id from MAS_PRODUCT where classify_id in (select id from MAS_CLASSIFY where parents_id in (select id from MAS_CLASSIFY where parents_id = :classify3)))",nativeQuery = true)
    List<Stock> findByClassify3(@Param("locationId") String locationId,@Param("classify3") String classify3);

    @Query(value = "select * from ACC_STOCK where LOCATION_ID = :locationId " +
            "and (PRODUCT_ID = :text or (select name from MAS_PRODUCT where id = PRODUCT_ID) like CONCAT('%',CONCAT(:text,'%'))) order by CUSTODY_QTY desc",nativeQuery = true)
    List<Stock> findByNameOrId(@Param("locationId") String locationId, @Param("text") String text);

    @Query(value = "select sum(CUSTODY_QTY) custodyQty,sum(USABLE_QTY) usableQty from ACC_STOCK where LOCATION_ID = :locationId ",nativeQuery = true)
    Map findSum(@Param("locationId") String locationId);

    @Query(value = "" +
            " SELECT USABLE_QTY, FROZEN_QTY, USABLE_QTY - FROZEN_QTY SURPLUS_USABLE_QTY FROM ACC_STOCK WHERE LOCATION_ID = :locationId AND PRODUCT_ID = :productId AND USABLE_QTY - FROZEN_QTY > 0 ", nativeQuery = true)
    List<Map> queryUsableQtyGTZerro(@Param("locationId") String locationId, @Param("productId") String productId);

    @Query("select distinct p from Product p left join Stock s on p = s.product where s.location.depot.dept.id = :deptId " +
            "and (p.id like %:productId% or p.barcode like %:productId% or p.name like %:productId%)")
    List<Product> findProductByStockLocationId(@Param("deptId") String deptId,@Param("productId")String productId);


    @Query(value = "" +
            "SELECT A.LOCATION_ID, A.PRODUCT_ID, A.COMPANY_ID, (SELECT ID FROM MAS_PRODUCT_UOM WHERE UOM_CATEGORY_ID  = A.PRODUCT_ID AND FACTOR = 1 and VALID = 1 and rownum = 1) PRODUCT_UOM_ID, " +
            "       CASE WHEN A.USABLE_QTY > B.BALANCE_QTY THEN B.BALANCE_QTY ELSE A.USABLE_QTY END USABLE_QTY " +
            "  FROM ACC_STOCK A, " +
            "       (SELECT SUM(BALANCE_QTY - FROST_QTY) BALANCE_QTY, COMPANY_ID, PRODUCT_ID FROM ACC_INVENTORY_BATCH L WHERE EXISTS (SELECT 1 FROM MAS_PARTY_ROLE T WHERE ROLE_ID = 'Supplier' AND ID = L.SUPPLIER_ID) GROUP BY COMPANY_ID, PRODUCT_ID) B " +
            " WHERE A.COMPANY_ID = B.COMPANY_ID " +
            "   AND A.PRODUCT_ID = B.PRODUCT_ID " +
            "   AND USABLE_QTY > 0 AND BALANCE_QTY > 0 " +
            "   AND A.LOCATION_ID = :flocationId AND A.PRODUCT_ID = :productId ", nativeQuery = true)
    Map availableStockAllSupplier(@Param("flocationId") String flocationId, @Param("productId") String productId);

    @Query(value = "" +
            "SELECT A.LOCATION_ID, A.PRODUCT_ID, A.COMPANY_ID, (SELECT ID FROM MAS_PRODUCT_UOM WHERE UOM_CATEGORY_ID  = A.PRODUCT_ID AND FACTOR = 1 and VALID = 1 and rownum = 1) PRODUCT_UOM_ID, " +
            "       CASE WHEN A.USABLE_QTY > B.BALANCE_QTY THEN B.BALANCE_QTY ELSE A.USABLE_QTY END USABLE_QTY " +
            "  FROM ACC_STOCK A, " +
            "       (SELECT SUM(BALANCE_QTY - FROST_QTY) BALANCE_QTY, COMPANY_ID, PRODUCT_ID FROM ACC_INVENTORY_BATCH WHERE SUPPLIER_ID IN (:supplierIds) GROUP BY COMPANY_ID, PRODUCT_ID) B " +
            " WHERE A.COMPANY_ID = B.COMPANY_ID " +
            "   AND A.PRODUCT_ID = B.PRODUCT_ID " +
            "   AND USABLE_QTY > 0 AND BALANCE_QTY > 0 " +
            "   AND A.LOCATION_ID = :flocationId AND A.PRODUCT_ID = :productId ", nativeQuery = true)
    Map availableStockSupplier(@Param("flocationId") String flocationId, @Param("productId") String productId, @Param("supplierIds") List supplierIds);


    /** 可用库存 */
    @Query(value = "" +
            "SELECT A.LOCATION_ID, A.PRODUCT_ID, A.COMPANY_ID, (SELECT ID FROM MAS_PRODUCT_UOM WHERE UOM_CATEGORY_ID  = A.PRODUCT_ID AND FACTOR = 1 and VALID = 1 and rownum = 1) PRODUCT_UOM_ID, " +
            "       CASE WHEN A.USABLE_QTY > B.BALANCE_QTY THEN B.BALANCE_QTY ELSE A.USABLE_QTY END USABLE_QTY " +
            "  FROM ACC_STOCK A, " +
            "       (SELECT SUM(BALANCE_QTY - FROST_QTY) BALANCE_QTY, COMPANY_ID, PRODUCT_ID FROM ACC_INVENTORY_BATCH GROUP BY COMPANY_ID, PRODUCT_ID) B " +
            " WHERE A.COMPANY_ID = B.COMPANY_ID " +
            "   AND A.PRODUCT_ID = B.PRODUCT_ID " +
            "   AND USABLE_QTY > 0 AND BALANCE_QTY > 0 " +
            "   AND A.LOCATION_ID = :flocationId AND A.PRODUCT_ID = :productId ", nativeQuery = true)
    Map availableStock(@Param("flocationId") String flocationId, @Param("productId") String productId);

    @Query(value = "" +
            "SELECT A.LOCATION_ID, A.PRODUCT_ID, A.COMPANY_ID, (SELECT ID FROM MAS_PRODUCT_UOM WHERE UOM_CATEGORY_ID  = A.PRODUCT_ID AND FACTOR = 1 and VALID = 1 and rownum = 1) PRODUCT_UOM_ID, " +
            "       CASE WHEN A.USABLE_QTY > B.BALANCE_QTY THEN B.BALANCE_QTY ELSE A.USABLE_QTY END USABLE_QTY " +
            "  FROM ACC_STOCK A, " +
            "       (SELECT SUM(BALANCE_QTY - FROST_QTY) BALANCE_QTY, COMPANY_ID, PRODUCT_ID FROM ACC_INVENTORY_BATCH L " +
            "          WHERE EXISTS (SELECT 1 FROM MAS_PARTY_ROLE T WHERE ROLE_ID = 'Supplier' AND PARTY_ID NOT IN (SELECT PARTY_ID FROM MAS_PARTY_ROLE T WHERE ROLE_ID = 'Company') AND ID = L.SUPPLIER_ID) " +
            "            AND EXISTS (SELECT 1 FROM ACC_CONTRACT WHERE STATE IN ('approve', 'extension') AND VALID_PERIOD >= GET_DATE() AND CONTRACT_TYPE = 'Contract.contractType.supplier' AND PARTNER_ID = L.SUPPLIER_ID) " +
            "         GROUP BY COMPANY_ID, PRODUCT_ID) B " +
            " WHERE A.COMPANY_ID = B.COMPANY_ID " +
            "   AND A.PRODUCT_ID = B.PRODUCT_ID " +
            "   AND USABLE_QTY > 0 AND BALANCE_QTY > 0 " +
            "   AND A.LOCATION_ID = :flocationId AND A.PRODUCT_ID = :productId ", nativeQuery = true)
    Map availableStockAllSupplierContract(@Param("flocationId") String flocationId, @Param("productId") String productId); // 直营转赋能特殊控制, 必须要有合同才能转

    @Query(value = "" +
            "SELECT A.LOCATION_ID, A.PRODUCT_ID, A.COMPANY_ID, (SELECT ID FROM MAS_PRODUCT_UOM WHERE UOM_CATEGORY_ID  = A.PRODUCT_ID AND FACTOR = 1 and VALID = 1 and rownum = 1) PRODUCT_UOM_ID, " +
            "       CASE WHEN A.USABLE_QTY > B.BALANCE_QTY THEN B.BALANCE_QTY ELSE A.USABLE_QTY END USABLE_QTY " +
            "  FROM ACC_STOCK A, " +
            "       (SELECT SUM(BALANCE_QTY - FROST_QTY) BALANCE_QTY, COMPANY_ID, PRODUCT_ID FROM ACC_INVENTORY_BATCH J " +
            "          WHERE SUPPLIER_ID IN (:supplierIds) " +
            "            AND NOT EXISTS (SELECT 1 FROM MAS_PARTY_ROLE T WHERE ROLE_ID = 'Supplier' AND PARTY_ID IN (SELECT PARTY_ID FROM MAS_PARTY_ROLE T WHERE ROLE_ID = 'Company') AND ID = J.SUPPLIER_ID) " +
            "            AND EXISTS (SELECT 1 FROM ACC_CONTRACT WHERE STATE IN ('approve', 'extension') AND VALID_PERIOD >= GET_DATE() AND CONTRACT_TYPE = 'Contract.contractType.supplier' AND PARTNER_ID = J.SUPPLIER_ID) " +
            "        GROUP BY COMPANY_ID, PRODUCT_ID) B " +
            " WHERE A.COMPANY_ID = B.COMPANY_ID " +
            "   AND A.PRODUCT_ID = B.PRODUCT_ID " +
            "   AND USABLE_QTY > 0 AND BALANCE_QTY > 0 " +
            "   AND A.LOCATION_ID = :flocationId AND A.PRODUCT_ID = :productId ", nativeQuery = true)
    Map availableStockSupplierContract(@Param("flocationId") String flocationId, @Param("productId") String productId, @Param("supplierIds") List supplierIds);

    @Query(value = "" +
            "SELECT A.LOCATION_ID, A.PRODUCT_ID, A.COMPANY_ID, (SELECT ID FROM MAS_PRODUCT_UOM WHERE UOM_CATEGORY_ID  = A.PRODUCT_ID AND FACTOR = 1 and VALID = 1 and rownum = 1) PRODUCT_UOM_ID, " +
            "       CASE WHEN A.USABLE_QTY > B.BALANCE_QTY THEN B.BALANCE_QTY ELSE A.USABLE_QTY END USABLE_QTY " +
            "  FROM ACC_STOCK A, " +
            "       (SELECT SUM(BALANCE_QTY - FROST_QTY) BALANCE_QTY, COMPANY_ID, PRODUCT_ID FROM ACC_INVENTORY_BATCH J " +
            "          WHERE SUPPLIER_ID IN (:supplierIds) " +
            "            AND NOT EXISTS (SELECT 1 FROM MAS_PARTY_ROLE T WHERE ROLE_ID = 'Supplier' AND PARTY_ID IN (SELECT PARTY_ID FROM MAS_PARTY_ROLE T WHERE ROLE_ID = 'Company') AND ID = J.SUPPLIER_ID) " +
            "            AND EXISTS (SELECT 1 FROM ACC_CONTRACT WHERE STATE IN ('approve', 'extension') AND VALID_PERIOD >= GET_DATE() AND PARTNER_ID = J.SUPPLIER_ID) " +
            "        GROUP BY COMPANY_ID, PRODUCT_ID) B " +
            " WHERE A.COMPANY_ID = B.COMPANY_ID " +
            "   AND A.PRODUCT_ID = B.PRODUCT_ID " +
            "   AND USABLE_QTY > 0 AND BALANCE_QTY > 0 " +
            "   AND A.LOCATION_ID = :flocationId AND A.PRODUCT_ID = :productId ", nativeQuery = true)
    Map availableStockSupplierContract2(@Param("flocationId") String flocationId, @Param("productId") String productId, @Param("supplierIds") List supplierIds);

    @Query(value = "SELECT NVL((SELECT (C.USABLE_QTY + C.TRANSFER_RECEIVE_QTY + " +
            "                    C.PURCHASING_WAY_QTY + C.TRANSFER_IN_BILLING_QTY + " +
            "                    C.REQUIRE_BILLING_QTY + C.SALES_CONTROL_QTY - " +
            "                    C.FROZEN_QTY) STOCK_AMOUNT " +
            "              FROM ACC_STOCK C, MAS_LOCATION D " +
            "             WHERE C.LOCATION_ID = D.ID " +
            "               AND D.USAGE_ID = '1' " +
            "               AND D.ID = :locationId " +
            "               AND C.PRODUCT_ID = A.ID " +
            "               AND D.VALID = 1), " +
            "            0) STOCK_AMOUNT " +
            "  FROM MAS_PRODUCT A, MAS_CATEGORY B, MAS_BRAND C " +
            " WHERE A.CATEGORY_ID = B.ID " +
            "   AND A.BRAND_ID = C.ID " +
            "   AND A.STATE <> '3' " +
            "   AND A.PRODUCT_GROUP_ID IN ('10', '20', '50') " +
            "   AND A.ID = :productId ", nativeQuery = true)
    Map findProductStock(@Param("productId") String productId, @Param("locationId") String locationId);


}
























